package com.pro01.backend.mapper;

import com.pro01.backend.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {

    /**
     * 插入用户（默认 role 为 USER）
     */
    @Insert("INSERT INTO users (username, password, email, role, credit, virtual_ratio, level, upload_count, download_count, status, passkey, created_at, updated_at) " +
            "VALUES (#{username}, #{password}, #{email}, #{role}, #{credit}, #{virtualRatio}, #{level}, #{uploadCount}, #{downloadCount}, #{status}, #{passkey}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertUser(User user);

    /**
     * 通过 ID 查找用户
     */
    @Select("SELECT * FROM users WHERE id = #{id}")
    User getUserById(Long id);

    /**
     * 用户查看自己的信息
     */
//    @Select("SELECT id, username, email, role, status, level, credit, virtual_ratio, upload_count, download_count,created_at " +
//            "FROM users WHERE id = #{id}")
//    User getUserSafeInfoById(Long id);
    @Select("""
    SELECT id, username, email, role, status, level, credit, virtual_ratio,
           upload_count, download_count, created_at,
           title, nickname_color, avatar_frame
    FROM users
    WHERE id = #{id}
""")
    User getUserSafeInfoById(@Param("id") Long id);


    /**
     * 通过用户名查找用户
     */
    @Select("SELECT * FROM users WHERE username = #{username}")
    User getUserByUsername(String username);

    /**
     * 通过邮箱查找用户
     */
    @Select("SELECT * FROM users WHERE email = #{email}")
    User getUserByEmail(String email);

    /**
     * 查询所有用户（管理员权限）
     */
    @Select("SELECT * FROM users")
    List<User> getAllUsers();

    @Select("SELECT id, username, email, role, status, level, credit, virtual_ratio, upload_count, download_count, created_at " +
            "FROM users " +
            "LIMIT #{offset}, #{pageSize}")
    List<User> getUsersPaged(@Param("offset") int offset, @Param("pageSize") int pageSize);

    @Select("SELECT COUNT(*) FROM users")
    int countUsers();

    /**
     * 更新用户信息（不修改用户名和邮箱）
     */
    /*
    @Update("UPDATE users SET role=#{role}, upload=#{upload}, download=#{download}, " +
            "share_ratio=#{shareRatio}, status=#{status}, updated_at=NOW() WHERE id = #{id}")
    int updateUser(User user);
     */
    @Update("UPDATE users SET " +
            "role = #{role}, " +
            "credit = #{credit}, " +
            "virtual_ratio = #{virtualRatio}, " +
            "level = #{level}, " +
            "upload_count = #{uploadCount}, " +
            "download_count = #{downloadCount}, " +
            "status = #{status}, " +
            "updated_at = NOW() " +
            "WHERE id = #{id}")
    int updateUser(User user);

    /**
     * 查询贡献值前10的用户
     */
    @Select("SELECT id, username, level, credit " +
            "FROM users " +
            "ORDER BY credit DESC " +
            "LIMIT 10")
    List<Map<String, Object>> getTop10UsersByCredit();

    /**
     * 查询准分享率前10的用户
     */
    @Select("SELECT username, level, virtual_ratio FROM users ORDER BY virtual_ratio DESC LIMIT 10")
    List<Map<String, Object>> getTop10UsersByVirtualRatio();

    // 查询贡献值排行榜前10
    @Select("SELECT * FROM users ORDER BY credit DESC LIMIT #{limit}")
    List<User> getTopUsersByCredit(@Param("limit") int limit);

    // 查询自己的贡献值排名
    @Select("""
    SELECT COUNT(*) + 1 
    FROM users 
    WHERE credit > (SELECT credit FROM users WHERE id = #{userId})
""")
    Integer getRankByCredit(@Param("userId") Long userId);

    // 查询准分享率排行榜前10
    @Select("SELECT * FROM users ORDER BY virtual_ratio DESC LIMIT #{limit}")
    List<User> getTopUsersByRatio(@Param("limit") int limit);

    // 查询自己的准分享率排名
    @Select("""
    SELECT COUNT(*) + 1 
    FROM users 
    WHERE virtual_ratio > (SELECT virtual_ratio FROM users WHERE id = #{userId})
""")
    Integer getRankByRatio(@Param("userId") Long userId);

    /**
     * 下面是模糊查询用户的语句
     */
    //支持分页查询
    @Select("""
    SELECT id, username, email, role, level, credit, virtual_ratio
    FROM users
    WHERE username LIKE CONCAT('%', #{keyword}, '%')
    LIMIT #{offset}, #{pageSize}
""")
    List<User> searchUsersByUsername(@Param("keyword") String keyword,
                                     @Param("offset") int offset,
                                     @Param("pageSize") int pageSize);


    // 查询符合条件的用户数量
    @Select("SELECT COUNT(*) FROM users WHERE username LIKE CONCAT('%', #{keyword}, '%')")
    int countUsersByUsername(@Param("keyword") String keyword);

    /**
     * 更新贡献值，准分享率，等级
     */
    @Update("UPDATE users SET credit = #{credit}, virtual_ratio = #{virtualRatio}, level = #{level}, updated_at = NOW() WHERE id = #{userId}")
    int updateCreditAndLevel(@Param("userId") Long userId, @Param("credit") Long credit, @Param("virtualRatio") Float virtualRatio, @Param("level") String level);

    @Update("UPDATE users SET credit = #{credit}, virtual_ratio = #{virtualRatio}, level = #{level}, upload_count = #{uploadCount}, download_count = #{downloadCount} WHERE id = #{userId}")
    int updateCreditAndLevelAndCounts(
            @Param("userId") Long userId,
            @Param("credit") Long credit,
            @Param("virtualRatio") Float virtualRatio,
            @Param("level") String level,
            @Param("uploadCount") Integer uploadCount,
            @Param("downloadCount") Integer downloadCount
    );

    /**
     * 更新促销奖励
     */
    @Update("UPDATE users SET " +
            "bonus_credit = bonus_credit + #{bonusCredit}, " +
            "bonus_upload = bonus_upload + #{bonusUpload}, " +
            "bonus_download = bonus_download + #{bonusDownload}, " +
            "updated_at = NOW() " +
            "WHERE id = #{userId}")
    int updateBonusValues(@Param("userId") Long userId,
                          @Param("bonusCredit") int bonusCredit,
                          @Param("bonusUpload") int bonusUpload,
                          @Param("bonusDownload") int bonusDownload);

    /**
     * 修改密码（用户自己操作）
     */
    @Update("UPDATE users SET password = #{password}, updated_at = NOW() WHERE id = #{id}")
    int updatePassword(@Param("id") Long id, @Param("password") String password);

    /**
     * 修改用户名
     */
    @Select("SELECT COUNT(*) > 0 FROM users WHERE username = #{username}")
    boolean existsByUsername(@Param("username") String username);

    @Update("UPDATE users SET username = #{newUsername} WHERE id = #{userId}")
    int updateUsername(@Param("userId") Long userId, @Param("newUsername") String newUsername);

    /**
     * 更新用户称号
     */
    @Update("UPDATE users SET title = #{title} WHERE id = #{userId}")
    int updateUserTitle(@Param("userId") Long userId, @Param("title") String title);

    /**
     * 更新用户名称颜色
     */
    @Update("UPDATE users SET nickname_color = #{color} WHERE id = #{userId}")
    int updateNicknameColor(@Param("userId") Long userId, @Param("color") String color);

    /**
     * 更新用户头像框
     */
    @Update("UPDATE users SET avatar_frame = #{frame} WHERE id = #{userId}")
    int updateAvatarFrame(@Param("userId") Long userId, @Param("frame") String frame);

    /**
     * 查询所有用户的贡献值和等级（仅管理员）
     */
//    @Select("SELECT id AS user_id, username, credit, level " +
//            "FROM users " +
//            "ORDER BY credit DESC")
//    List<Map<String, Object>> getAllUsersCreditAndLevel();
    @Select("SELECT id AS user_id, username, credit, level " +
            "FROM users " +
            "ORDER BY credit DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<Map<String, Object>> getAllUsersCreditAndLevelPaged(@Param("offset") int offset, @Param("pageSize") int pageSize);

    @Select("SELECT COUNT(*) FROM users")
    int countAllUsers();  // 总记录数

    /**
     * 查询所有用户的准分享率（仅管理员）
     */
//    @Select("SELECT id AS user_id, username, virtual_ratio " +
//            "FROM users " +
//            "ORDER BY virtual_ratio DESC")
//    List<Map<String, Object>> getAllUsersVirtualRatio();
// 查询分页的准分享率
    @Select("SELECT id AS user_id, username, virtual_ratio " +
            "FROM users " +
            "ORDER BY virtual_ratio DESC " +
            "LIMIT #{limit} OFFSET #{offset}")
    List<Map<String, Object>> getUsersVirtualRatioPaged(@Param("offset") int offset, @Param("limit") int limit);

    /**
     * 查询指定用户的所有信息（仅管理员）
     */
    @Select("SELECT id, username, email, role, status, level, credit, virtual_ratio, upload_count, download_count, created_at, updated_at " +
            "FROM users WHERE id = #{id}")
    Map<String, Object> getSafeUserInfoById(@Param("id") Long id);


    /**
     * 封禁用户（仅管理员）
     */
    @Update("UPDATE users SET status = 'BANNED', updated_at=NOW() WHERE id = #{id}")
    int banUser(Long id);

    /**
     * 解封用户（仅管理员）
     */
    @Update("UPDATE users SET status = 'ACTIVE', updated_at=NOW() WHERE id = #{id}")
    int unbanUser(@Param("id") Long id);

    /**
     * 物理删除用户（仅管理员）
     */
    @Delete("DELETE FROM users WHERE id = #{id}")
    int deleteUser(Long id);

    /**
     * 提拔用户为管理员（仅管理员）
     */
    @Update("UPDATE users SET role = 'ADMIN', updated_at = NOW() WHERE id = #{id}")
    int updateRole(@Param("id") Long id);

    @Update("UPDATE users SET role = 'ADMIN' WHERE id = #{userId}")
    int promoteToAdmin(@Param("userId") Long userId);


    /**
     * 降级用户为普通用户（仅管理员）
     */
    @Update("UPDATE users SET role = 'USER', updated_at = NOW() WHERE id = #{id} AND role = 'ADMIN'")
    int updateRoleToUser(@Param("id") Long id);

    /**
     * 警告用户（仅管理员）
     */
    @Update("UPDATE users SET status = 'WARNED', updated_at = NOW() WHERE id = #{id}")
    int updateStatus(@Param("id") Long id);

    /**
     * 解除警告（仅管理员）
     */
    @Update("UPDATE users SET status = 'ACTIVE', updated_at = NOW() WHERE id = #{id} AND status = 'WARNED'")
    int updateStatusToActive(@Param("id") Long id);

    /**
     * 管理员发送系统通知
     */
    @Select("""
    SELECT id
    FROM users
""")
    List<Long> getAllUserIds();

}
